package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import exceptions.DBException;

import auxilary.Configurations;
import auxilary.Log;

public class DBCreator {

	public LinkedList<String> createTables;
	public LinkedList<String> deleteTables;

	DBHandler dh = new DBHandler();

	String url = Configurations.DB.URL;
	String dbName = url.split("/")[url.split("/").length - 1];
	String userName = Configurations.DB.USERNAME;
	String password = Configurations.DB.PASSWORD;
	protected String createIf = null;
	protected String ending = " ENGINE = MyIsam DEFAULT CHARACTER SET = utf8;";
	private String drop = new String("DROP TABLE IF EXISTS `" + dbName + "`.");

	public void closeAll(Connection c) {
		dh.closeConnection(c);
	}

	public void closeAll(ResultSet rs) {
		dh.closeConnection(rs);
	}

	public DBCreator() {
		String urls[] = url.split("/");
		dbName = urls[urls.length - 1];
		createIf = "CREATE  TABLE IF NOT EXISTS `" + dbName + "`.";
		createTables = new LinkedList<String>();
		deleteTables = new LinkedList<String>();

		initTables(createTables, deleteTables, createIf, ending, drop);
	}

	public static void initTables(LinkedList<String> create,
			LinkedList<String> tearDown, String createIf, String ending,
			String drop) {
		// create the 'user' table
		String user = createIf + "`" + Tables.User.dbName + "` (" + "`"
				+ Tables.User.Columns.ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + Tables.User.Columns.USERNAME
				+ "` VARCHAR(45) NOT NULL UNIQUE," + "`"
				+ Tables.User.Columns.PASSWORD + "` VARCHAR(45) NOT NULL ,"
				+ "`" + Tables.User.Columns.FIRST_NAME + "` VARCHAR(45) NULL ,"
				+ "`" + Tables.User.Columns.LAST_NAME + "` VARCHAR(45) NULL ,"
				+ "`" + Tables.User.Columns.EMAIL + "` VARCHAR(45) NULL ,"
				+ "`" + Tables.User.Columns.FACULTY + "` VARCHAR(45) NULL ,"
				+ "`" + Tables.User.Columns.SEMESTER + "` INT ," + "`"
				+ Tables.User.Columns.IS_ADMIN
				+ "` TINYINT(1)  NULL DEFAULT '0' ," + "`"
				+ Tables.User.Columns.PHONE + "` VARCHAR(45) NULL ,"
				+ "PRIMARY KEY (`" + Tables.User.Columns.ID + "`) " + ") "
				+ ending;
		Log.dbg(user);
		create.add(user);
		tearDown.addFirst(new String(drop + "`" + Tables.User.dbName + "`;"));

		// create the 'course' table
		String course = createIf + "`" + Tables.Course.dbName + "` (" + "`"
				+ Tables.Course.Columns.ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + Tables.Course.Columns.NAME + "` VARCHAR(45) NOT NULL UNIQUE,"
				+ "`" + Tables.Course.Columns.DESCRIPTION + "` BLOB ," + "`"
				+ Tables.Course.Columns.CREDITS + "` TINYINT(1) DEFAULT '0' ,"
				+ "`" + Tables.Course.Columns.CAPACITY + "` INT DEFAULT '0' ,"
				+ "`" + Tables.Course.Columns.IS_REMOVED
				+ "` TINYINT(1)  NULL DEFAULT '0' ," + "`"
				+ Tables.Course.Columns.GROUP_ID + "` INT  NULL DEFAULT '0' ,"
				+ "`" + Tables.Course.Columns.CREATOR_ID
				+ "` INT NOT NULL ,"
				+ "  PRIMARY KEY (`" + Tables.Course.Columns.ID + "`) " + ", "
				+ "INDEX `" + Tables.Course.Fk.CREATOR + "` (`"
				+ Tables.Course.Columns.CREATOR_ID + "` ASC) ,"
				+ "CONSTRAINT `" + Tables.Course.Fk.CREATOR
				+ "` FOREIGN KEY (`" + Tables.Course.Columns.CREATOR_ID + "` )"
				+ "REFERENCES `" + Tables.SCHEMA + "`.`"
				+ Tables.User.dbName + "` (`" + Tables.User.Columns.ID
				+ "` )" + " ON DELETE CASCADE ON UPDATE NO ACTION " + ") "
				+ ending;
		Log.dbg(course);
		create.add(course);
		tearDown.addFirst(new String(drop + "`" + Tables.Course.dbName + "`;"));

		// create the 'Session' table
		String session = createIf + "`" + Tables.Session.dbName + "` (" + "`"
				+ Tables.Session.Columns.ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + Tables.Session.Columns.START_SLOT
				+ "` TINYINT(7) NOT NULL," + "`"
				+ Tables.Session.Columns.END_SLOT + "` TINYINT(7) NOT NULL,"
				+ "`" + Tables.Course.Columns.IS_REMOVED
				+ "` TINYINT(1)  NULL DEFAULT '0' ," + "`"
				+ Tables.Session.Columns.COURSE_ID + "` INT NOT NULL,"
				+ "  PRIMARY KEY (`" + Tables.Session.Columns.ID + "`), "
				+ "INDEX `" + Tables.Session.Fk.COURSE + "` (`"
				+ Tables.Session.Columns.COURSE_ID + "` ASC) ,"
				+ "CONSTRAINT `" + Tables.Session.Fk.COURSE
				+ "` FOREIGN KEY (`" + Tables.Session.Columns.COURSE_ID + "` )"
				+ "REFERENCES `" + Tables.SCHEMA + "`.`"
				+ Tables.Session.dbName + "` (`" + Tables.Course.Columns.ID
				+ "` )" + "ON DELETE CASCADE ON UPDATE NO ACTION " + ") "
				+ ending;
		Log.dbg(session);
		create.add(session);
		tearDown.addFirst(new String(drop + "`" + Tables.Session.dbName + "`;"));

		// create the table 'tags'
		String tags = createIf + "`" + Tables.Tags.dbName + "` (" + "`"
				+ Tables.Tags.Columns.ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + Tables.Tags.Columns.NAME + "` VARCHAR(45) NOT NULL ,"
				+ " PRIMARY KEY (`" + Tables.Tags.Columns.ID + "`) " + ") " + ending;
		Log.dbg(tags);
		create.add(tags);
		tearDown.addFirst(new String(drop + "`" + Tables.Tags.dbName + "`;"));

		// create the table 'projects2tags'
		String course2tag = createIf + "`" + Tables.CourseToTag.dbName + "` ("
				+ "`" + Tables.CourseToTag.Columns.COURSE_ID
				+ "` INT NOT NULL ," + "`"
				+ Tables.CourseToTag.Columns.TAG_ID
				+ "` INT NOT NULL," + "PRIMARY KEY (`"
				+ Tables.CourseToTag.Columns.TAG_ID + "`, `"
				+ Tables.CourseToTag.Columns.COURSE_ID + "`), " + "INDEX `"
				+ Tables.CourseToTag.Fk.COURSE + "` (`"
				+ Tables.CourseToTag.Columns.COURSE_ID + "` ASC) ," + "INDEX `"
				+ Tables.CourseToTag.Fk.TAG + "` (`"
				+ Tables.CourseToTag.Columns.TAG_ID + "` ASC) ,"
				+ "CONSTRAINT `" + Tables.CourseToTag.Fk.COURSE
				+ "` FOREIGN KEY (`" + Tables.CourseToTag.Columns.COURSE_ID
				+ "` )" + "REFERENCES `" + Tables.SCHEMA + "`.`"
				+ Tables.Course.dbName + "` (`" + Tables.Course.Columns.ID
				+ "` )" + "ON DELETE NO ACTION ON UPDATE NO ACTION,"
				+ "CONSTRAINT `" + Tables.CourseToTag.Fk.TAG
				+ "` FOREIGN KEY (`" + Tables.CourseToTag.Columns.TAG_ID
				+ "` )" + "REFERENCES `" + Tables.SCHEMA + "`.`"
				+ Tables.Tags.dbName + "` (`" + Tables.Tags.Columns.ID + "` )"
				+ "ON DELETE NO ACTION ON UPDATE NO ACTION" + ") " + ending;
		Log.dbg(course2tag);
		create.add(course2tag);
		tearDown.addFirst(new String(drop + "`" + Tables.CourseToTag.dbName
				+ "`;"));

		// create the table 'projects2tags'
		String course2user = createIf + "`" + Tables.CourseToUser.dbName
				+ "` (" + "`" + Tables.CourseToUser.Columns.COURSE_ID
				+ "` INT NOT NULL ," + "`"
				+ Tables.CourseToUser.Columns.USER_ID
				+ "` INT NOT NULL," + "`"
				+ Tables.CourseToUser.Columns.IS_REGISTERED
				+ "` TINYINT(1)  NULL DEFAULT '0' ," + "PRIMARY KEY (`"
				+ Tables.CourseToUser.Columns.USER_ID + "`, `"
				+ Tables.CourseToUser.Columns.COURSE_ID + "`), " + "INDEX `"
				+ Tables.CourseToUser.Fk.COURSE + "` (`"
				+ Tables.CourseToUser.Columns.COURSE_ID + "` ASC) ,"
				+ "INDEX `" + Tables.CourseToUser.Fk.USER + "` (`"
				+ Tables.CourseToUser.Columns.USER_ID + "` ASC) ,"
				+ "CONSTRAINT `" + Tables.CourseToUser.Fk.COURSE
				+ "` FOREIGN KEY (`" + Tables.CourseToUser.Columns.COURSE_ID
				+ "` )" + "REFERENCES `" + Tables.SCHEMA + "`.`"
				+ Tables.Course.dbName + "` (`" + Tables.Course.Columns.ID
				+ "` )" + "ON DELETE NO ACTION ON UPDATE NO ACTION,"
				+ "CONSTRAINT `" + Tables.CourseToUser.Fk.USER
				+ "` FOREIGN KEY (`" + Tables.CourseToUser.Columns.USER_ID
				+ "` )" + "REFERENCES `" + Tables.SCHEMA + "`.`"
				+ Tables.User.dbName + "` (`" + Tables.User.Columns.ID + "` )"
				+ "ON DELETE NO ACTION ON UPDATE NO ACTION" + ") " + ending;
		Log.dbg(course2user);
		create.add(course2user);
		tearDown.addFirst(new String(drop + "`" + Tables.CourseToUser.dbName
				+ "`;"));
		
		// create the 'user' table
		String style = createIf + "`" + Tables.Styles.dbName + "` (" + "`"
				+ Tables.Styles.Columns.ID + "` INT NOT NULL AUTO_INCREMENT ,"
				+ "`" + Tables.Styles.Columns.NAME
				+ "` VARCHAR(45) NOT NULL UNIQUE,"
				+ "`" + Tables.Styles.Columns.FILE + "` BLOB NULL ,"
				+ "PRIMARY KEY (`" + Tables.Styles.Columns.ID + "`) " + ") "
				+ ending;
		Log.dbg(style);
		create.addFirst(style);
		tearDown.add(new String(drop + "`" + Tables.Styles.dbName + "`;"));

	}

	public void execList(LinkedList<String> commandList) {
		Connection con = null;
		try {
			con = dh.getConnection();
			Statement stmt = con.createStatement();
			for (String command : commandList) {
				System.out.println(command);
				try{
					stmt.executeUpdate(command);
				} catch (Exception e) {
					System.out.println("skip");
				}
			}
			stmt.close();
		} catch (Exception e) {
			Log.err("DBCreator: [execList] Exception " + e.toString());
		} finally {
			dh.closeConnection(con);
		}
		return;
	}

	public void createDatabase() {
		execList(createTables);
		return;
	}

	public void deleteDatabase() {
		execList(deleteTables);
		return;

	}

	public void clearTable(String table) {
		Connection con = null;
		try {
			con = dh.getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("DELETE FROM " + table + ";");
		} catch (SQLException e) {
			Log.err("DBCreator: [clearTable] SQL");
		} catch (DBException e) {
			Log.err("DBCreator: [clearTable] DBEception");
		} finally {
			dh.closeConnection(con);
		}

	}

	public static void main(String args[]) {

		// Change the connection string according to your db, ip, username and
		// password
		DBCreator dbc = new DBCreator();

		dbc.deleteDatabase();
		dbc.createDatabase();

	}
}
